/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package Controller;

import Module.User;
import java.sql.*;
import java.util.ArrayList;
/**
 *
 * @author CCD Gruppe 2
 */
public class VoiceListController {
    /**
     * Object used to connect to the database
     */
    Database database = new Database();

    /**
     * Method used to add a user to the voicelist table
     * @param projectId
     * @param userId
     */
    private boolean checkIfAlreadyInVoiceList(int projectId, int userId){
        Connection conn = null;
        boolean value = true;
        try{
            conn = DriverManager.getConnection(database.getURL());
            PreparedStatement s = conn.prepareStatement(""
                    + "select * from voiceList where userId=? and projectId=?");
            s.setInt(2, projectId);
            s.setInt(1, userId);
            ResultSet rs = s.executeQuery();
            while(rs.next()){
                value=false;
            }
            rs.close();
            conn.close();
            return value;
        }catch(Exception e){
            System.err.println(e);
        }
        return value;
    }

    public boolean insertIntoVoiceList(int projectId, int userId){
        Connection conn = null;
        try{
            if(checkIfAlreadyInVoiceList(projectId,userId)){
                conn = DriverManager.getConnection(database.getURL());
                PreparedStatement s = conn.prepareStatement(""
                    + "insert into voiceList values(DEFAULT,?,?)");
                s.setInt(1, projectId);
                s.setInt(2, userId);
                s.executeUpdate();
                conn.close();
                return true;
            }
            
        }catch(Exception e){
            System.err.println(e);
        }
        return false;
    }

    /**
     * Method used to remove a user from the voicelist table
     * @param projectId
     * @param userId
     */
    public void removeFromVoiceList(int projectId, int userId){
        Connection conn = null;
        try{
            conn = DriverManager.getConnection(database.getURL());
            PreparedStatement s = conn.prepareStatement(""
                    + "delete from voiceList where projectId=? and userId=?");
            s.setInt(1, projectId);
            s.setInt(2, userId);
            s.executeUpdate();
            conn.close();
        }catch(Exception e){
            System.err.println(e);
        }
    }
    /**
     * Method used to remove a voicelist from a project
     * @param projectId
     */
    public void removeVoiceList(int projectId){
      Connection conn = null;
        try{
            conn = DriverManager.getConnection(database.getURL());
            PreparedStatement s = conn.prepareStatement(""
                    + "delete from voiceList where projectId=?");
            s.setInt(1, projectId);
            s.executeUpdate();
            conn.close();
        }catch(Exception e){
            System.err.println(e);
        }
    }

    /**
     * Method used to return all the users added to a voicelist to a project
     * @param projectId
     * @return
     */
    public ArrayList<User> getUsersAddedToVoiceList(int projectId){
        ArrayList<User> userList = new ArrayList<User>();
        Connection conn = null;
        try{
            conn = DriverManager.getConnection(database.getURL());
            PreparedStatement s = conn.prepareStatement("select users.* from voicelist left join users on (voicelist.userid=users.userId) where"
                    + " projectId =? order by voicelist.counter asc");
            s.setInt(1, projectId);
            ResultSet rs = s.executeQuery();
            while(rs.next()){
                userList.add(new User(rs.getInt(1),rs.getString(2), rs.getString(3), rs.getString(4), rs.getString(5), rs.getString(6)));
            }
        }catch(Exception e){
            System.err.println(e);
        }

        return userList;
    }
}
